import mysql from "mysql2/promise";

// 类型定义
interface DatabaseConfig {
  host: string;
  user: string;
  password: string;
  database: string;
  port: number;
  waitForConnections: boolean;
  connectionLimit: number;
  queueLimit: number;
  acquireTimeout: number;
  timeout: number;
}

// 数据库配置 - 对应我们之前创建的数据库表
const dbConfig: DatabaseConfig = {
  host: process.env.DB_HOST || "localhost",
  user: process.env.DB_USER || "root",
  password: process.env.DB_PASSWORD || "123456",
  database: process.env.DB_NAME || "campus_emergency_db",
  port: parseInt(process.env.DB_PORT || "3306"),
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  acquireTimeout: 60000,
  timeout: 60000,
};

// 创建连接池
let pool: mysql.Pool | null = null;

const createPool = async (): Promise<mysql.Pool> => {
  try {
    if (!pool) {
      pool = mysql.createPool(dbConfig);

      // 测试连接
      const connection = await pool.getConnection();
      console.log("✅ 数据库连接成功 - Emergency Admin Database");
      connection.release();
    }
    return pool;
  } catch (error) {
    console.error("❌ 数据库连接池创建失败:", error);
    throw error;
  }
};

// 获取连接池
const getPool = (): mysql.Pool => {
  if (!pool) {
    throw new Error("数据库连接池未初始化. 请先调用 createPool() 方法.");
  }
  return pool;
};

// 执行查询
const query = async (
  sql: string,
  params: any[] = []
): Promise<{ rows: any[]; fields: any[] }> => {
  try {
    const connection = await getPool().getConnection();
    const [rows, fields] = await connection.execute(sql, params);
    connection.release();
    return { rows: rows as any[], fields: fields as mysql.FieldPacket[] };
  } catch (error) {
    console.error("数据库查询错误:", error);
    throw error;
  }
};

// 事务查询
const transaction = async (
  callback: (connection: mysql.PoolConnection) => Promise<void>
): Promise<void> => {
  const connection = await getPool().getConnection();
  try {
    await connection.beginTransaction();
    await callback(connection);
    await connection.commit();
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
};

export { createPool, getPool, query, transaction };
